# -*-coding:utf-8-*-
from datetime import datetime
import cx_Oracle
import os
import re
import zipfile
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By

# username = "ISPSHE"
# pwd = "HORN123"
# logitech_url = "https://external-qa.logitech.com"  # 罗技测试
# search_result_url = "https://external-qa.logitech.com/OA_HTML/RF.jsp?function_id=53766&resp_id=51495&resp_appl_id=60005&security_group_id=0&lang_code=US&oas=pV3QzY9duoTbuSPVhFLj6g..&params=5ww0XeRGssAfQXfFWxssKennD2uVoPkcyhkuaqWBYvY"
# def click_upload(driver):
#     # 点击LOGI LSTAR Supplier User(zip file)文件夹
#     li = WebDriverWait(driver, wait_time).until(
#         EC.element_to_be_clickable((By.XPATH, "//li[@class='rootmenu'][3]/a")))
#     li.click()
#     # 点击LOGI_LSTAR_Supplier_User_(zip_file)
#     WebDriverWait(li, wait_time).until(
#         EC.element_to_be_clickable((By.XPATH, "//li[@id='LILOGI_LSTAR_Supplier_User_(zip_file)']/a"))).click()

username = "ISPSHE"
pwd = "ISPSHE"
logitech_url = "https://external.logitech.com/OA_HTML/RF.jsp?function_id=28716&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=WXOOczlJ_tpQDKESiNJt-Q..&params=Nhx5JwzlPILM4yWjmzc2Cg"  # 罗技正式
search_result_url = "https://external.logitech.com/OA_HTML/RF.jsp?function_id=53766&resp_id=51495&resp_appl_id=60005&security_group_id=0&lang_code=US&oas=czjOeHwHHvFvx6YRv6DUew..&params=VpZGbrZG8mI6BdxJf.1J8-CkcH-AR8ZJ00AoiEDCIZw"
Transactions_url = "https://external.logitech.com/OA_HTML/RF.jsp?function_id=53781&resp_id=51495&resp_appl_id=60005&security_group_id=0&lang_code=US&oas=rl5FQPpXqOXyQh7G7ZsO8A..&params=VpZGbrZG8mI6BdxJf.1J8-CkcH-AR8ZJ00AoiEDCIZw"
from send_email import m
def click_upload(driver):
    # 点击LOGI LSTAR Supplier User(zip file)文件夹
    li = WebDriverWait(driver, wait_time).until(
        EC.element_to_be_clickable((By.XPATH, "//ul[@id='treemenu1']/li[{}]/a".format(m))))
    li.click()

# 邮件收件人
add1 = "cruise.zhang@szhorn.com"
add2 = "it02@szhorn.com"
add3 = "marvin.zhang@szhorn.com"
add4 = "zling@szhorn.com"
add5 = "lisa.qiu@szhorn.com"
add6 = "oqc@szhorn.com"
add7 = "yhq@szhorn.com"
add8 = "sys_dev@szhorn.com"  # 备用

def to_adds():
    adds = []
    adds.append(add1)
    adds.append(add2)
    adds.append(add3)
    adds.append(add4)
    adds.append(add5)
    adds.append(add6)
    adds.append(add7)
    adds.append(add8)
    return adds

wait_time = 30

# EIP
EIP_username = "00000"
EIP_password = "Hornit123"
EIP_url = "http://192.168.110.205:8087/"

# 数据库连接
dsnStr = cx_Oracle.makedsn("192.168.110.205", 1521, "EIP")
conn = cx_Oracle.connect("EIP", "EIP", dsnStr, threaded=True)
c = conn.cursor()

# UPLOAD_STATUS
e0 = "E0"  # EIP导出失败(EIP访问,登录,网络异常等python运行方面原因)
e1 = "E1"  # EIP导出失败(PO号错误,EIP无数据等非python运行方面原因)
e2 = "E2"  # 上传罗技系统失败(访问,登录,数据定位等)
s0 = "S0"  # EIP导出成功, 未上传
s1 = "S1"  # 上传成功, 查询有结果
s2 = "S2"  # 上传操作完成,Search Results无结果/查询异常
s3 = "S3"  # master_batch_id OK, 无txn_batch_id

# EIP导出保存路径(CSV格式)
save_csv_files = os.path.dirname(os.path.abspath(__file__)) + "\\csv_format\\files_address"
# EIP导出保存路径(ZIP格式)
save_zip_files = os.path.dirname(os.path.abspath(__file__)) + "\\ZIP_format\\zip_files"

# 已上传文件保存路径
pro_files = os.path.dirname(os.path.abspath(__file__)) + "\\process_files"


# 以PO号查询上传结果
def select_result(po):
    sql = "select QUERY_RESULT, QTY, UPLOAD_STATUS, UPLOAD_RESULT from R_OUTCOM_CONFIG where PO='{}'".format(po)
    res = c.execute(sql).fetchone()
    return res


# 提取PO号
def select_eip_po():
    sql = "select PO from R_OUTCOM_CONFIG where ORG_CODE = '001001' and (UPLOAD_STATUS not in ('S1','OLD') or UPLOAD_STATUS is Null)"
    pos = c.execute(sql).fetchall()
    return pos


# 更改数据表状态UPLOAD_STATUS
def update_po(statu_num, result, po_num):
    UPDATE_TIME = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
    if not(statu_num and result):
        sql = "update R_OUTCOM_CONFIG set LAST_TIME=to_date('{}','yyyy/MM/dd HH24:mi:ss') where PO='{}'".format(UPDATE_TIME, po_num)
    else:
        sql = "update R_OUTCOM_CONFIG set UPLOAD_STATUS='{}', UPLOAD_RESULT='{}', LAST_TIME=to_date('{}','yyyy/MM/dd HH24:mi:ss') where PO='{}'".format(statu_num, result, UPDATE_TIME, po_num)
    try:
        c.execute(sql)
        conn.commit()
    except Exception as e:
        # print(e, "{}上传状态修改失败".format(po_num))
        conn.rollback()


def files_list():
    l = []
    zip_files = os.path.dirname(os.path.abspath(__file__)) + "\\ZIP_format\\zip_files"
    for root, dirs, files in os.walk(zip_files):
        for d in dirs:
            file_dir = os.path.join(zip_files, d)
            l .append(file_dir)
    return l


def files_csv_list():
    l = []
    csv_files = os.path.dirname(os.path.abspath(__file__)) + "\\csv_format\\files_address"
    for root, dirs, files in os.walk(csv_files):
        for d in dirs:
            file_dir = os.path.join(csv_files, d)
            l .append(file_dir)
    return l


# ZIP格式文件压缩
def zipDir(dirpath, outFullName):
    zip = zipfile.ZipFile(outFullName, "w", zipfile.ZIP_DEFLATED)
    for path, dirnames, filenames in os.walk(dirpath):
        # 去掉目标跟路径，只对目标文件夹下边的文件及文件夹进行压缩
        fpath = path.replace(dirpath, '')
        for filename in filenames:
            zip.write(os.path.join(path, filename), os.path.join(fpath, filename))
    zip.close()

def past_date(d):
    # if d.month == 3:
    #     m = 12
    #     y = d.year - 1
    # elif d.month == 2:
    #     m = 11
    #     y = d.year - 1
    # elif d.month == 1:
    #     m = 10
    #     y = d.year - 1
    # else:
    #     m = d.month - 3
    #     y = d.year
    m = d.month
    y = d.year - 1
    from_date = "{}-{}-{}".format(1, m, y)
    return from_date

